package com.managertrade.controller;

import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.managertrade.common.Result;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;

import javax.sql.DataSource;
import java.util.*;

@RestController
@RequestMapping("/api/sqlite")
@CrossOrigin(origins = "*") // 跨域
public class SqliteDynamicController {

    private final JdbcTemplate sqliteJdbc;
    private final JdbcTemplate mysqlJdbc;
    private final ObjectMapper objectMapper = new ObjectMapper();

    public SqliteDynamicController(
            @Qualifier("sqliteDataSource") DataSource sqliteDataSource,
            @Qualifier("mysqlDataSource") DataSource mysqlDataSource) {
        this.sqliteJdbc = new JdbcTemplate(sqliteDataSource);
        this.mysqlJdbc = new JdbcTemplate(mysqlDataSource);
    }

    private JdbcTemplate getJdbc(String dbType) {
        if ("mysql".equalsIgnoreCase(dbType)) return mysqlJdbc;
        return sqliteJdbc; // 默认 sqlite
    }

    /** 获取表列表 */
    @GetMapping("/tables")
    public Result<List<String>> listTables(@RequestParam String dbType) {
        JdbcTemplate jdbc = getJdbc(dbType);
        List<String> tables;
        if ("mysql".equalsIgnoreCase(dbType)) {
            tables = jdbc.queryForList("SHOW TABLES", String.class);
        } else {
            tables = jdbc.queryForList("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;", String.class);
        }
        return Result.success(tables);
    }
    /** 查询表数据 */
    @GetMapping("/query")
    public Result<Map<String, Object>> queryTable(
            @RequestParam String dbType,
            @RequestParam String tableName,
            @RequestParam(defaultValue = "1") int page,
            @RequestParam(defaultValue = "10") int size,
            @RequestParam(required = false) String filtersJson,
            @RequestParam(required = false) String orderBy,
            @RequestParam(required = false, defaultValue = "ASC") String orderDirection
    ) throws Exception {
        JdbcTemplate jdbc = getJdbc(dbType);

        // 解析过滤条件
        Map<String, Map<String, Object>> filters = new HashMap<>();
        if (filtersJson != null && !filtersJson.isEmpty()) {
            filters = objectMapper.readValue(filtersJson, new TypeReference<Map<String, Map<String, Object>>>() {});
        }

        StringBuilder where = new StringBuilder(" WHERE 1=1 ");
        List<Object> params = new ArrayList<>();
        for (Map.Entry<String, Map<String, Object>> e : filters.entrySet()) {
            Map<String, Object> f = e.getValue();
            String operator = f.getOrDefault("operator", "LIKE").toString();
            Object value = f.get("value");
            if (value != null) {
                if ("LIKE".equalsIgnoreCase(operator)) {
                    where.append(" AND ").append(e.getKey()).append(" LIKE ? ");
                    params.add("%" + value + "%");
                } else {
                    where.append(" AND ").append(e.getKey()).append(" ").append(operator).append(" ? ");
                    params.add(value);
                }
            }
        }

        // 先查总数
        int total = jdbc.queryForObject("SELECT COUNT(*) FROM " + tableName + where, params.toArray(), Integer.class);

        // 排序逻辑（安全性处理）
        String orderSql = "";
        if (orderBy != null && !orderBy.trim().isEmpty()) {
            // 防止 SQL 注入：只允许字母/数字/下划线
            if (!orderBy.matches("^[a-zA-Z0-9_]+$")) {
                throw new IllegalArgumentException("非法的排序字段: " + orderBy);
            }
            String direction = "ASC".equalsIgnoreCase(orderDirection) ? "ASC" : "DESC";
            orderSql = " ORDER BY " + orderBy + " " + direction;
        }

        // 分页
        int offset = (page - 1) * size;
        params.add(size);
        params.add(offset);

        // 拼接最终查询
        String dataSql = "SELECT * FROM " + tableName + where + orderSql + " LIMIT ? OFFSET ?";

        List<Map<String, Object>> rows = jdbc.queryForList(dataSql, params.toArray());

        Map<String, Object> result = new HashMap<>();
        result.put("columns", rows.isEmpty() ? new ArrayList<>() : rows.get(0).keySet());
        result.put("rows", rows);
        result.put("total", total);
        result.put("page", page);
        result.put("size", size);

        return Result.success(result);
    }

    /** 插入数据 */
    @PostMapping("/insert")
    public Result<?> insertRow(
            @RequestParam String dbType,
            @RequestParam String tableName,
            @RequestBody Map<String, Object> values) {

        if (values.isEmpty()) return Result.error("插入数据不能为空");

        JdbcTemplate jdbc = getJdbc(dbType);

        StringBuilder sql = new StringBuilder("INSERT INTO ").append(tableName).append(" (");
        StringBuilder placeholders = new StringBuilder("(");
        List<Object> params = new ArrayList<>();

        for (String key : values.keySet()) {
            sql.append(key).append(", ");
            placeholders.append("?, ");
            params.add(values.get(key));
        }
        sql.setLength(sql.length() - 2);
        placeholders.setLength(placeholders.length() - 2);
        sql.append(") VALUES ").append(placeholders).append(")");

        int rows = jdbc.update(sql.toString(), params.toArray());
        return rows > 0 ? Result.success() : Result.error("插入失败");
    }

    /** 更新数据 */
    @PostMapping("/update")
    public Result<?> updateRow(
            @RequestParam String dbType,
            @RequestParam String tableName,
            @RequestParam String idColumn,
            @RequestParam Object idValue,
            @RequestBody Map<String, Object> updates) {

        if (updates.isEmpty()) return Result.error("没有需要更新的字段");

        JdbcTemplate jdbc = getJdbc(dbType);

        StringBuilder sql = new StringBuilder("UPDATE ").append(tableName).append(" SET ");
        List<Object> params = new ArrayList<>();
        for (String key : updates.keySet()) {
            sql.append(key).append(" = ?, ");
            params.add(updates.get(key));
        }
        sql.setLength(sql.length() - 2);
        sql.append(" WHERE ").append(idColumn).append(" = ?");
        params.add(idValue);

        int rows = jdbc.update(sql.toString(), params.toArray());
        return rows > 0 ? Result.success() : Result.error("更新失败");
    }

    /** 删除数据 */
    @PostMapping("/delete")
    public Result<?> deleteRow(
            @RequestParam String dbType,
            @RequestParam String tableName,
            @RequestParam String idColumn,
            @RequestParam Object idValue) {

        JdbcTemplate jdbc = getJdbc(dbType);
        String sql = "DELETE FROM " + tableName + " WHERE " + idColumn + " = ?";
        int rows = jdbc.update(sql, idValue);
        return rows > 0 ? Result.success() : Result.error("删除失败");
    }
}
